 1.电商分析之--广告业务之广告效果分析
   
   1).需求分析
   活动曝光效果评估：
   行为(曝光、点击、购买)、时间段、广告位、商品，统计对应的次数
   时间段、广告位、商品，曝光次数最多的前100个
   2).创建ADS层表
   drop table if exists ads.ads_ad_show_place;
create table ads.ads_ad_show_place(
ad_action tinyint,
hour string,
place string,
product_id int,
cnt bigint
)PARTITIONED BY (`dt` string)
row format delimited fields terminated by ',';
   
   drop table if exists ads.ads_ad_show_place_window;
create table ads.ads_ad_show_place_window(
hour string,
place string,
product_id int,
cnt bigint,
rank int
)PARTITIONED BY (`dt` string)
row format delimited fields terminated by ',';
   3).加载ADS层数据
   /data/lagoudw/script/advertisement/ads_load_ad_show_page.sh

#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table ads.ads_ad_show_place
partition (dt='$do_date')
select ad_action,
       hour,
	   place,
       product_id,
       count(1)
from dwd.dwd_ad
where dt='$do_date'
group by ad_action, hour, place, product_id;
"

hive -e "$sql"

   /data/lagoudw/script/advertisement/ads_load_ad_show_page_window.sh

#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table ads.ads_ad_show_place_window
partition (dt='$do_date')
select *
from (
      select hour,
             place,
             product_id,
             cnt,
             row_number() over (partition by hour, place,
product_id order by cnt desc) rank
from ads.ads_ad_show_place
where dt='$do_date' and ad_action='0'
) t
where rank <= 100
"

hive -e "$sql"
   
   小结：分析简单，没有DWS层
   Flume、json解析在会员分析讲解

 2.广告分析小结
   
   脚本调用次序:**
   ods_load_event_log.sh
   dwd_load_event_log.sh
   dwd_load_ad_log.sh
   
   ads_load_ad_show.sh
   ads_load_ad_show_rate.sh
   ads_load_ad_show_page.sh
   ads_load_ad_show_page_window.sh
   